// ============================================================================
//
// Copyright (C) 2006-2007 Dengues
//
// Google Group: http://groups.google.com/group/dengues
//
// QQ Group: 24885404
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program; if not, write to the Free Software
// Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
//
// ============================================================================
package org.dengues.ui.query;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import org.dengues.commons.IDenguesSharedImage;
import org.dengues.commons.utils.ImageUtil;
import org.dengues.core.metadata.types.MetadataTypesManager;
import org.dengues.model.database.DBColumn;
import org.dengues.model.database.DBTable;
import org.dengues.model.database.DatabaseDiagram;
import org.eclipse.emf.common.util.EList;
import org.eclipse.jface.dialogs.TitleAreaDialog;
import org.eclipse.jface.viewers.DoubleClickEvent;
import org.eclipse.jface.viewers.IDoubleClickListener;
import org.eclipse.jface.viewers.StructuredSelection;
import org.eclipse.jface.viewers.TreeViewer;
import org.eclipse.swt.SWT;
import org.eclipse.swt.custom.SashForm;
import org.eclipse.swt.events.ModifyEvent;
import org.eclipse.swt.events.ModifyListener;
import org.eclipse.swt.events.SelectionAdapter;
import org.eclipse.swt.events.SelectionListener;
import org.eclipse.swt.layout.GridData;
import org.eclipse.swt.layout.GridLayout;
import org.eclipse.swt.widgets.Button;
import org.eclipse.swt.widgets.Combo;
import org.eclipse.swt.widgets.Composite;
import org.eclipse.swt.widgets.Control;
import org.eclipse.swt.widgets.Label;
import org.eclipse.swt.widgets.Shell;
import org.eclipse.swt.widgets.Text;

/**
 * Qiang.Zhang.Adolf@gmail.com class global comment. Detailled comment <br/>
 * 
 * $Id: Dengues.epf 2007-12-12 qiang.zhang $
 * 
 */
public class ComputedColumnDialog extends TitleAreaDialog {

    private static final int DEFALUT_WIDTH = 70;

    public final static String[] QNAME_PATTERNS = new String[] { "Unqualified Name", "2-Part Name", "3-Part Name" };

    public final static List<String> STRING_FUNCTIONS = new ArrayList<String>();

    public final static List<String> NUMBERIC_FUNCTIONS = new ArrayList<String>();

    public final static List<String> TIME_FUNCTIONS = new ArrayList<String>();

    static {
        STRING_FUNCTIONS.add("ASCII");
        STRING_FUNCTIONS.add("BIN");
        STRING_FUNCTIONS.add("BIT_LENGTH");
        STRING_FUNCTIONS.add("CHAR");
        STRING_FUNCTIONS.add("CHARACTER_LENGTH");
        STRING_FUNCTIONS.add("CHAR_LENGTH");
        STRING_FUNCTIONS.add("CONCAT");
        STRING_FUNCTIONS.add("CONCAT_WS");
        STRING_FUNCTIONS.add("CONV");
        STRING_FUNCTIONS.add("ELT");
        STRING_FUNCTIONS.add("EXPORT_SET");
        STRING_FUNCTIONS.add("FIELD");
        STRING_FUNCTIONS.add("FIND_IN_SET");
        STRING_FUNCTIONS.add("HEX");
        STRING_FUNCTIONS.add("INSERT");
        STRING_FUNCTIONS.add("INSTR");
        STRING_FUNCTIONS.add("LCASE");
        STRING_FUNCTIONS.add("LEFT");
        STRING_FUNCTIONS.add("LENGTH");
        STRING_FUNCTIONS.add("LOAD_FILE");
        STRING_FUNCTIONS.add("LOCATE");
        STRING_FUNCTIONS.add("LOWER");
        STRING_FUNCTIONS.add("LPAD");
        STRING_FUNCTIONS.add("LTRIM");
        STRING_FUNCTIONS.add("MAKE_SET");
        STRING_FUNCTIONS.add("MATCH");
        STRING_FUNCTIONS.add("MID");
        STRING_FUNCTIONS.add("OCT");
        STRING_FUNCTIONS.add("OCTET_LENGTH");
        STRING_FUNCTIONS.add("ORD");
        STRING_FUNCTIONS.add("POSITION");
        STRING_FUNCTIONS.add("QUOTE");
        STRING_FUNCTIONS.add("REPEAT");
        STRING_FUNCTIONS.add("REPLACE");
        STRING_FUNCTIONS.add("REVERSE");
        STRING_FUNCTIONS.add("RIGHT");
        STRING_FUNCTIONS.add("RPAD");
        STRING_FUNCTIONS.add("RTRIM");
        STRING_FUNCTIONS.add("SOUNDEX");
        STRING_FUNCTIONS.add("SPACE");
        STRING_FUNCTIONS.add("STRCMP");
        STRING_FUNCTIONS.add("SUBSTRING");
        STRING_FUNCTIONS.add("SUBSTRING_INDEX");
        STRING_FUNCTIONS.add("TRIM");
        STRING_FUNCTIONS.add("UCASE");
        STRING_FUNCTIONS.add("UPPER");

        NUMBERIC_FUNCTIONS.add("ABS");
        NUMBERIC_FUNCTIONS.add("ACOS");
        NUMBERIC_FUNCTIONS.add("ASIN");
        NUMBERIC_FUNCTIONS.add("ATAN");
        NUMBERIC_FUNCTIONS.add("ATAN2");
        NUMBERIC_FUNCTIONS.add("BIT_COUNT");
        NUMBERIC_FUNCTIONS.add("CEILING");
        NUMBERIC_FUNCTIONS.add("COS");
        NUMBERIC_FUNCTIONS.add("COT");
        NUMBERIC_FUNCTIONS.add("DEGREES");
        NUMBERIC_FUNCTIONS.add("EXP");
        NUMBERIC_FUNCTIONS.add("FLOOR");
        NUMBERIC_FUNCTIONS.add("LOG");
        NUMBERIC_FUNCTIONS.add("LOG10");
        NUMBERIC_FUNCTIONS.add("MAX");
        NUMBERIC_FUNCTIONS.add("MIN");
        NUMBERIC_FUNCTIONS.add("MOD");
        NUMBERIC_FUNCTIONS.add("PI");
        NUMBERIC_FUNCTIONS.add("POW");
        NUMBERIC_FUNCTIONS.add("POWER");
        NUMBERIC_FUNCTIONS.add("RADIANS");
        NUMBERIC_FUNCTIONS.add("RAND");
        NUMBERIC_FUNCTIONS.add("ROUND");
        NUMBERIC_FUNCTIONS.add("SIN");
        NUMBERIC_FUNCTIONS.add("SQRT");
        NUMBERIC_FUNCTIONS.add("TAN");
        NUMBERIC_FUNCTIONS.add("TRUNCATE");

        TIME_FUNCTIONS.add("CURDATE");
        TIME_FUNCTIONS.add("CURRENT_DATE");
        TIME_FUNCTIONS.add("CURRENT_TIME");
        TIME_FUNCTIONS.add("CURRENT_TIMESTAMP");
        TIME_FUNCTIONS.add("CURTIME");
        TIME_FUNCTIONS.add("DATE_FORMAT");
        TIME_FUNCTIONS.add("DAYNAME");
        TIME_FUNCTIONS.add("DAYOFMONTH");
        TIME_FUNCTIONS.add("DAYOFWEEK");
        TIME_FUNCTIONS.add("DAYOFYEAR");
        TIME_FUNCTIONS.add("FROM_DAYS");
        TIME_FUNCTIONS.add("FROM_UNIXTIME");
        TIME_FUNCTIONS.add("HOUR");
        TIME_FUNCTIONS.add("MINUTE");
        TIME_FUNCTIONS.add("MONTH");
        TIME_FUNCTIONS.add("MONTHNAME");
        TIME_FUNCTIONS.add("NOW");
        TIME_FUNCTIONS.add("PERIOD_ADD");
        TIME_FUNCTIONS.add("PERIOD_DIFF");
        TIME_FUNCTIONS.add("QUARTER");
        TIME_FUNCTIONS.add("SECOND");
        TIME_FUNCTIONS.add("SEC_TO_TIME");
        TIME_FUNCTIONS.add("TIME_FORMAT");
        TIME_FUNCTIONS.add("TIME_TO_SEC");
        TIME_FUNCTIONS.add("TO_DAYS");
        TIME_FUNCTIONS.add("UNIX_TIMESTAMP");
        TIME_FUNCTIONS.add("WEEK");
        TIME_FUNCTIONS.add("WEEKDAY");
        TIME_FUNCTIONS.add("YEAR");
    }

    private DatabaseDiagram dbDiagram;

    private Text expressionText;

    private String name;

    private String expression;

    private final String message = "Provide the name of computed column. Specify the function of column from function. Data type of computed column is for union query.";

    private Collection<String> existNames;

    /**
     * Qiang.Zhang.Adolf@gmail.com ComputedColumnDialog constructor comment.
     * 
     * @param parentShell
     */
    public ComputedColumnDialog(Shell parentShell) {
        super(parentShell);
        setShellStyle(getShellStyle() | SWT.RESIZE);
        setTitleImage(ImageUtil.getImage(IDenguesSharedImage.WIZARD_CALCOLUMN));
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com ComputedColumnDialog constructor comment.
     * 
     * @param activeShell
     * @param dbDiagram
     * @param existNames
     */
    public ComputedColumnDialog(Shell activeShell, DatabaseDiagram dbDiagram, Collection<String> existNames) {
        this(activeShell);
        this.dbDiagram = dbDiagram;
        this.existNames = existNames;
    }

    public ComputedColumnDialog(Shell activeShell, DatabaseDiagram dbDiagram, String name, String expression,
            Collection<String> existNames) {
        this(activeShell, dbDiagram, existNames);
        this.name = name;
        this.expression = expression;
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com ComputedColumnDialog constructor comment.
     * 
     * @param shell
     * @param dbDiagram2
     */
    public ComputedColumnDialog(Shell shell, DatabaseDiagram dbDiagram2) {
        this(shell, dbDiagram2, new ArrayList<String>());
    }

    /*
     * (non-Javadoc)
     * 
     * @see org.eclipse.jface.window.Window#configureShell(org.eclipse.swt.widgets.Shell)
     */
    @Override
    protected void configureShell(Shell newShell) {
        super.configureShell(newShell);
        newShell.setText("Computed Column");
        newShell.setSize(650, 550);
    }

    /*
     * (non-Javadoc)
     * 
     * @see org.eclipse.jface.dialogs.TitleAreaDialog#createDialogArea(org.eclipse.swt.widgets.Composite)
     */
    @Override
    protected Control createDialogArea(Composite parent) {
        Composite container = (Composite) super.createDialogArea(parent);
        setTitle("Computed Column");
        setMessage(message);
        GridData gridData = new GridData(GridData.FILL_BOTH);
        container.setLayoutData(gridData);
        Composite nameComp = new Composite(container, SWT.NONE);
        GridLayout gridLayout = new GridLayout(2, false);
        gridData = new GridData(GridData.FILL_HORIZONTAL);
        nameComp.setLayout(gridLayout);
        nameComp.setLayoutData(gridData);
        Label nameLabel = new Label(nameComp, SWT.NONE);
        nameLabel.setText("Name");
        final Text nameText = new Text(nameComp, SWT.BORDER);
        gridData = new GridData(GridData.FILL_HORIZONTAL);
        nameText.setLayoutData(gridData);
        if (name != null) {
            nameText.setText(name);
        }
        nameText.addModifyListener(new ModifyListener() {

            /*
             * (non-Javadoc)
             * 
             * @see org.eclipse.swt.events.ModifyListener#modifyText(org.eclipse.swt.events.ModifyEvent)
             */
            public void modifyText(ModifyEvent e) {
                name = nameText.getText();
                updateStatus();
            }
        });

        Composite composite = new Composite(container, SWT.NONE);
        gridLayout = new GridLayout();
        gridData = new GridData(GridData.FILL_BOTH);
        composite.setLayout(gridLayout);
        composite.setLayoutData(gridData);
        SashForm contents = new SashForm(composite, SWT.HORIZONTAL);
        gridData = new GridData(GridData.FILL_BOTH);
        contents.setLayoutData(gridData);
        Composite leftComp = new Composite(contents, SWT.BORDER);
        gridLayout = new GridLayout();
        gridData = new GridData(GridData.FILL_BOTH);
        leftComp.setLayout(gridLayout);
        leftComp.setLayoutData(gridData);
        createLeftComposite(leftComp);
        Composite rightComp = new Composite(contents, SWT.BORDER);
        gridLayout = new GridLayout();
        gridData = new GridData(GridData.FILL_BOTH);
        rightComp.setLayout(gridLayout);
        rightComp.setLayoutData(gridData);
        createRightComposite(rightComp);
        contents.setWeights(new int[] { 2, 5 });
        updateStatus();
        return container;
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "updateStatus".
     */
    private void updateStatus() {
        if (name == null || name.length() == 0) {
            setErrorMessage("the column name can't empty.");
        } else if (existNames.contains(name)) {
            setErrorMessage("the column name always exist.");
        } else {
            setErrorMessage(null);
            setMessage(message);
        }
    }

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "createRightComposite".
     * 
     * @param rightComp
     */
    private void createRightComposite(Composite rightComp) {
        Label label = new Label(rightComp, SWT.NONE);
        label.setText("Expression:");
        GridData gridData = new GridData(GridData.FILL_HORIZONTAL);
        label.setLayoutData(gridData);
        expressionText = new Text(rightComp, SWT.BORDER | SWT.MULTI | SWT.V_SCROLL | SWT.H_SCROLL);
        gridData = new GridData(GridData.FILL_BOTH);
        gridData.heightHint = 10 * expressionText.getLineHeight();
        expressionText.setLayoutData(gridData);
        if (expression != null) {
            expressionText.setText(getExpression());
        }
        expressionText.addModifyListener(new ModifyListener() {

            /*
             * (non-Javadoc)
             * 
             * @see org.eclipse.swt.events.ModifyListener#modifyText(org.eclipse.swt.events.ModifyEvent)
             */
            public void modifyText(ModifyEvent e) {
                expression = expressionText.getText();
            }
        });

        Composite opComposite = new Composite(rightComp, SWT.NONE);
        gridData = new GridData(GridData.FILL_HORIZONTAL);
        gridData.horizontalAlignment = GridData.END;
        GridLayout gridLayout = new GridLayout(10, false);
        opComposite.setLayout(gridLayout);
        opComposite.setLayoutData(gridData);
        Button button = new Button(opComposite, SWT.PUSH);
        button.setText("+");
        button.addSelectionListener(listener);
        button = new Button(opComposite, SWT.PUSH);
        button.setText("-");
        button.addSelectionListener(listener);
        button = new Button(opComposite, SWT.PUSH);
        button.setText("*");
        button.addSelectionListener(listener);
        button = new Button(opComposite, SWT.PUSH);
        button.setText("/");
        button.addSelectionListener(listener);
        button = new Button(opComposite, SWT.PUSH);
        button.setText("=");
        button.addSelectionListener(listener);
        button = new Button(opComposite, SWT.PUSH);
        button.setText("''");
        button.addSelectionListener(listener);
        button = new Button(opComposite, SWT.PUSH);
        button.setText("||");
        button.addSelectionListener(listener);
        button = new Button(opComposite, SWT.PUSH);
        button.setText("()");
        button.addSelectionListener(listener);

        Composite typeComposite = new Composite(rightComp, SWT.NONE);
        gridData = new GridData(GridData.FILL_HORIZONTAL);
        gridLayout = new GridLayout(2, false);
        typeComposite.setLayout(gridLayout);
        typeComposite.setLayoutData(gridData);
        label = new Label(typeComposite, SWT.NONE);
        label.setText("String:");
        gridData = new GridData();
        gridData.widthHint = DEFALUT_WIDTH;
        label.setLayoutData(gridData);
        Combo combo = new Combo(typeComposite, SWT.READ_ONLY | SWT.BORDER);
        combo.setItems(STRING_FUNCTIONS.toArray(new String[0]));
        gridData = new GridData(GridData.FILL_HORIZONTAL);
        combo.setLayoutData(gridData);
        combo.select(0);
        combo.addSelectionListener(listener);

        typeComposite = new Composite(rightComp, SWT.NONE);
        gridData = new GridData(GridData.FILL_HORIZONTAL);
        gridLayout = new GridLayout(2, false);
        typeComposite.setLayout(gridLayout);
        typeComposite.setLayoutData(gridData);
        label = new Label(typeComposite, SWT.NONE);
        label.setText("Numberic:");
        gridData = new GridData();
        gridData.widthHint = DEFALUT_WIDTH;
        label.setLayoutData(gridData);
        combo = new Combo(typeComposite, SWT.READ_ONLY | SWT.BORDER);
        combo.setItems(NUMBERIC_FUNCTIONS.toArray(new String[0]));
        gridData = new GridData(GridData.FILL_HORIZONTAL);
        combo.setLayoutData(gridData);
        combo.select(0);
        combo.addSelectionListener(listener);

        typeComposite = new Composite(rightComp, SWT.NONE);
        gridData = new GridData(GridData.FILL_HORIZONTAL);
        gridLayout = new GridLayout(2, false);
        typeComposite.setLayout(gridLayout);
        typeComposite.setLayoutData(gridData);
        label = new Label(typeComposite, SWT.NONE);
        label.setText("Time/Date:");
        gridData = new GridData();
        gridData.widthHint = DEFALUT_WIDTH;
        label.setLayoutData(gridData);
        combo = new Combo(typeComposite, SWT.READ_ONLY | SWT.BORDER);
        combo.setItems(TIME_FUNCTIONS.toArray(new String[0]));
        gridData = new GridData(GridData.FILL_HORIZONTAL);
        combo.setLayoutData(gridData);
        combo.select(0);
        combo.addSelectionListener(listener);

        typeComposite = new Composite(rightComp, SWT.NONE);
        gridData = new GridData(GridData.FILL_HORIZONTAL);
        gridLayout = new GridLayout(2, false);
        typeComposite.setLayout(gridLayout);
        typeComposite.setLayoutData(gridData);
        label = new Label(typeComposite, SWT.NONE);
        label.setText("Data Type:");
        gridData = new GridData();
        gridData.widthHint = DEFALUT_WIDTH;
        label.setLayoutData(gridData);
        combo = new Combo(typeComposite, SWT.BORDER);
        combo.setItems(MetadataTypesManager.getInstance().getDBTypesLabels());
        gridData = new GridData(GridData.FILL_HORIZONTAL);
        combo.setLayoutData(gridData);
    }

    private final SelectionListener listener = new SelectionAdapter() {

        @Override
        public void widgetSelected(org.eclipse.swt.events.SelectionEvent e) {
            if (e.getSource() instanceof Button) {
                Button button = (Button) e.getSource();
                expressionText.insert(button.getText());
            } else if (e.getSource() instanceof Combo) {
                Combo button = (Combo) e.getSource();
                expressionText.insert(button.getText() + "()");
            }
        };
    };

    private TreeViewer dbTree;

    /**
     * Qiang.Zhang.Adolf@gmail.com Comment method "createLeftComposite".
     * 
     * @param leftComp
     */
    private void createLeftComposite(Composite leftComp) {
        Label label = new Label(leftComp, SWT.NONE);
        label.setText("Qualified name pattern:");
        GridData gridData = new GridData(GridData.FILL_HORIZONTAL);
        label.setLayoutData(gridData);
        Combo combo = new Combo(leftComp, SWT.READ_ONLY | SWT.BORDER);
        combo.setItems(QNAME_PATTERNS);
        combo.select(0);
        gridData = new GridData(GridData.FILL_HORIZONTAL);
        combo.setLayoutData(gridData);
        label = new Label(leftComp, SWT.NONE);
        label.setText("Columns:");
        gridData = new GridData(GridData.FILL_HORIZONTAL);
        label.setLayoutData(gridData);
        dbTree = QueryUiUtils.getInstance().createDbDiagramTree(leftComp, dbDiagram);
        dbTree.addDoubleClickListener(new IDoubleClickListener() {

            /*
             * (non-Javadoc)
             * 
             * @see org.eclipse.jface.viewers.IDoubleClickListener#doubleClick(org.eclipse.jface.viewers.DoubleClickEvent)
             */
            public void doubleClick(DoubleClickEvent event) {
                StructuredSelection selection = (StructuredSelection) event.getSelection();
                Object felemt = selection.getFirstElement();
                if (felemt instanceof DBColumn) {
                    DBColumn column = (DBColumn) felemt;
                    expressionText.insert(column.getTable().getName() + "." + column.getName());
                } else if (felemt instanceof DBTable) {
                    DBTable table = (DBTable) felemt;
                    EList columns = table.getColumns();
                    for (Object object : columns) {
                        expressionText.insert(" " + table.getName() + "." + ((DBColumn) object).getName());
                    }
                }
            }
        });
    }

    /**
     * Getter for name.
     * 
     * @return the name
     */
    public String getName() {
        return this.name;
    }

    /**
     * Getter for expression.
     * 
     * @return the expression
     */
    public String getExpression() {
        return this.expression;
    }

}
